package com.thinkgem.dingdan.modules.bu.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import com.thinkgem.dingdan.modules.bu.constant.BusinessConstant;
import com.thinkgem.dingdan.modules.bu.entity.BuOrder;

public class ExcelTest {

	public static HSSFWorkbook hssfWorkbook;
	public static HSSFSheet hssfSheet;

	/**
	 * 获取第一个Sheet中的单元格
	 * 
	 * @param rowIndex
	 * @param colIndex
	 * @return
	 */
	public static Cell getCellInSheet(int rowIndex, int colIndex) {
		hssfSheet = hssfWorkbook.getSheetAt(0);
		Row row = hssfSheet.getRow(rowIndex - 1);
		Cell cell = row.getCell(colIndex - 1);
		return cell;
	}

	public static void main(String[] args) {
		try {

			// reportOrderMergeXls(getEntity(), "zz", "yy");
			System.out.println("导出完成");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static BuOrder getEntity() {
		BuOrder order = new BuOrder();

		order.setId("0ec85e0f047449fc9bff67cb1b821ac6");
		order.setOrderNo("20180916201759");
		order.setOrderType("内部订单");
		order.setProductNo("1");
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		order.setOrderDate(format.format(new Date()));

		order.setShipDate(new Date());
		order.setProductName("1");
		order.setCrafts("1");
		order.setElectroplate("1");
		order.setBack("1");

		order.setPackages("1");
		order.setFitting("1");
		String picUrl = "http://image.baidu.com/search/detail?ct=503316480&z=0&ipn=d&word=%E5%9B%BE%E7%89%87&hs=0&pn=0&spn=0&di=156082395700&pi=0&rn=1&tn=baiduimagedetail&is=0%2C0&ie=utf-8&oe=utf-8&cl=2&lm=-1&cs=415293130%2C2419074865&os=1556766946%2C250663840&simid=4145280632%2C499508967&adpicid=0&lpn=0&ln=30&fr=ala&fm=&sme=&cg=&bdtype=0&oriquery=&objurl=http%3A%2F%2Fimg.zcool.cn%2Fcommunity%2F0117e2571b8b246ac72538120dd8a4.jpg%401280w_1l_2o_100sh.jpg&fromurl=ippr_z2C%24qAzdH3FAzdH3Fooo_z%26e3Bzv55s_z%26e3Bv54_z%26e3BvgAzdH3Fo56hAzdH3FZMTYoNTMzMDQ%3D_z%26e3Bip4s&gsm=0&islist=&querylist=";
		order.setPicUrl(picUrl);
		order.setMaterial("1");

		order.setQuantity("1");
		order.setSalesModelFee("1");
		order.setSalesPrice("1");
		order.setProduceFee("1");
		order.setProduceModelFee("1");

		order.setSalesTotalFee("1");
		order.setProduceTotalFee("1");
		order.setProfit("1");
		order.setCustomerName("业务员001");
		order.setSalesman("客户001");

		order.setSupplierName("厂商001");
		order.setOrderType("未发货");
		order.setDeliveryName("aaa");
		order.setDeliveryNumber("123");

		return order;
	}

	/**
	 * 导出xls文件(合并单元格)
	 * 
	 * @param order
	 * @param xlsFileName
	 * @param sheetName
	 */
	public static void reportOrderMergeXls(int orderFlag,BuOrder order, String xlsFileName, String sheetName,
			HttpServletResponse response, String httpPriffix, String winUserfilesBasedir, String linuxUserfilesBasedir)
			throws Exception {

		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

		HSSFWorkbook wb = new HSSFWorkbook();
		// excel生成过程: excel-->sheet-->row-->cell
		HSSFSheet sheet = wb.createSheet(sheetName);// 为excel创建一个名为xxx的sheet页
		// sheet.autoSizeColumn(0);// 自动设宽
		HSSFRow oneRow = sheet.createRow(0);
		HSSFRow secondRow = sheet.createRow(1);
		HSSFRow thirdRow = sheet.createRow(2);
		HSSFRow fourRow = sheet.createRow(3);
		HSSFRow fiveRow = sheet.createRow(4);

		HSSFRow sixRow = sheet.createRow(5);
		HSSFRow sevenRow = sheet.createRow(6);
		HSSFRow eightRow = sheet.createRow(7);
		HSSFRow nineRow = sheet.createRow(8);
		HSSFRow tenRow = sheet.createRow(9);

		HSSFRow elevenRow = sheet.createRow(10);
		HSSFRow twelveRow = sheet.createRow(11);
		HSSFRow thirteenRow = sheet.createRow(12);
		HSSFRow fourteenRow = sheet.createRow(13);
		HSSFRow fifteenRow = sheet.createRow(14);

		HSSFRow sixteenRow = sheet.createRow(36);
		HSSFRow seventeenRow = sheet.createRow(38);

		// 设置文本单元格风格，居中对齐.(通用添加边框)
		HSSFCellStyle styleMl = wb.createCellStyle();
		// 设置字体
		HSSFFont font01 = wb.createFont();
		font01.setFontName("黑体");
		font01.setFontHeightInPoints((short) 10);// 设置字体大小
		styleMl.setFont(font01);// 要用到的字体格式
		styleMl.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleMl.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		styleMl.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		styleMl.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		styleMl.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		
		
		// 设置文本单元格风格，居中对齐.(通用添加边框)备注
		HSSFCellStyle styleRemark = wb.createCellStyle();
		HSSFFont fontRmark = wb.createFont();
		fontRmark.setColor(HSSFColor.RED.index);
		fontRmark.setFontHeightInPoints((short) 10);
		fontRmark.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		styleRemark.setFont(fontRmark);
		if(StringUtils.isNotBlank(order.getRemarks()) && order.getRemarks().length()>=40){
			styleRemark.setWrapText(true);
		}
		styleRemark.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		styleRemark.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleRemark.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		styleRemark.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		styleRemark.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		styleRemark.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		

		// 设置单元格风格，居中对齐.(通用添加边框)
		HSSFCellStyle styleM2 = wb.createCellStyle();
		styleM2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleM2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleM2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		styleM2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		styleM2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		styleM2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		styleM2.setFont(font01);// 要用到的字体格式

		// 设置单元格风格，居中对齐.(标题添加边框)
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setFontName("黑体");
		font.setFontHeightInPoints((short) 18);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		style.setFont(font);// 要用到的字体格式
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 设置字体

		// 设置单元格风格，内容居中对齐.
		HSSFCellStyle wordStyle02 = wb.createCellStyle();
		HSSFFont words02 = wb.createFont();
		words02.setColor(HSSFColor.RED.index);
		words02.setFontHeightInPoints((short) 12);
		words02.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		wordStyle02.setFont(words02);
		wordStyle02.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		wordStyle02.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		wordStyle02.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		wordStyle02.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		wordStyle02.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		wordStyle02.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 设置单元格风格，备注信息左对齐.
		HSSFCellStyle wordStyle05 = wb.createCellStyle();
		HSSFFont words05 = wb.createFont();
		words05.setColor(HSSFColor.RED.index);
		words05.setFontHeightInPoints((short) 10);
		words05.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		wordStyle05.setFont(words05);
		wordStyle05.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		wordStyle05.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		wordStyle05.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		wordStyle05.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		wordStyle05.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		wordStyle05.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 设置单元格风格，通用左对齐.
		HSSFCellStyle wordStyle03 = wb.createCellStyle();
		HSSFFont words03 = wb.createFont();
		words03.setColor(HSSFColor.RED.index);
		words03.setFontHeightInPoints((short) 12);
		words03.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		wordStyle03.setFont(words03);
		wordStyle03.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		wordStyle03.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		wordStyle03.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		wordStyle03.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		wordStyle03.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		wordStyle03.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		// 创建第一行
		HSSFCell oneRowCellOne = oneRow.createCell(0);
		if(orderFlag == BusinessConstant.OUT_ORDER_TYPE_INT){
			oneRowCellOne.setCellValue(order.getSupplierName() + "生产单");
		}else if(orderFlag == BusinessConstant.INNER_ORDER_TYPE_INT){
			oneRowCellOne.setCellValue(order.getCreateUser() + "生产单");
		}

		// 创建第二行
		HSSFCell secondRowCellTwo = secondRow.createCell(0);
		secondRowCellTwo.setCellValue(order.getCrafts());

		HSSFCell secondRowCellSix = secondRow.createCell(6);
		secondRowCellSix.setCellValue("是否初订:");

		HSSFCell secondRowCellSeven = secondRow.createCell(7);
		if (order.getStatus().equals("0")) {
			secondRowCellSeven.setCellValue("初订");
		} else {
			secondRowCellSeven.setCellValue("续订");
		}

		// 创建第三行
		HSSFCell thirdRowCellOne = thirdRow.createCell(0);
		thirdRowCellOne.setCellValue("生产编号:");

		HSSFCell thirdRowCellTwo = thirdRow.createCell(1);
		thirdRowCellTwo.setCellValue(order.getProductNo());

		HSSFCell thirdRowCellFive = thirdRow.createCell(6);
		thirdRowCellFive.setCellValue("产品名称:");

		HSSFCell thirdRowCellSix = thirdRow.createCell(7);
		thirdRowCellSix.setCellValue(order.getProductName());

		// 创建第四行
		HSSFCell fourRowCellOne = fourRow.createCell(0);
		fourRowCellOne.setCellValue("生产数量:");

		HSSFCell fourRowCellTwo = fourRow.createCell(1);
		fourRowCellTwo.setCellValue(order.getQuantity());

		HSSFCell fourRowCellFive = fourRow.createCell(6);
		fourRowCellFive.setCellValue("下单日期:");

		HSSFCell fourRowCellSeven = fourRow.createCell(7);
		if (order.getOrderDate() != null && !("").equals(order.getOrderDate())) {
			fourRowCellSeven.setCellValue(order.getOrderDate());
		}

		// 创建第五行
		HSSFCell fiveRowCellOne = fiveRow.createCell(0);
		fiveRowCellOne.setCellValue("产品尺寸:");

		HSSFCell fiveRowCellRwo = fiveRow.createCell(1);
		fiveRowCellRwo.setCellValue(order.getProductSize());

		HSSFCell fiveRowCellThree = fiveRow.createCell(4);
		fiveRowCellThree.setCellValue("厚度:");

		HSSFCell fiveRowCellFour = fiveRow.createCell(5);
		fiveRowCellFour.setCellValue(order.getThinkness());

		HSSFCell fiveRowCellFive = fiveRow.createCell(6);
		fiveRowCellFive.setCellValue("出货日期:");

		HSSFCell fiveRowCellSix = fiveRow.createCell(7);
		if (order.getShipDate() != null && !order.getShipDate().equals("")) {
			fiveRowCellSix.setCellValue(format.format(order.getShipDate()));
		}

		// 创建第六行
		HSSFCell sixRowCellOne = sixRow.createCell(0);
		sixRowCellOne.setCellValue("材料");

		HSSFCell sixRowCellTwo = sixRow.createCell(1);
		sixRowCellTwo.setCellValue("背模");

		HSSFCell sixRowCellFour = sixRow.createCell(3);
		sixRowCellFour.setCellValue("配件");

		HSSFCell sixRowCellSix = sixRow.createCell(5);
		sixRowCellSix.setCellValue("电镀");

		HSSFCell sixRowCellSeven = sixRow.createCell(6);
		sixRowCellSeven.setCellValue("部门流程");

		HSSFCell sixRowCellEight = sixRow.createCell(7);
		sixRowCellEight.setCellValue("负责人签字");

		HSSFCell sixRowCellNine = sixRow.createCell(8);
		sixRowCellNine.setCellValue("完成日期");

		// 创建第七行
		HSSFCell sevenRowCellOne = sevenRow.createCell(0);
		sevenRowCellOne.setCellValue(order.getMaterial());

		HSSFCell sevenRowCellTwo = sevenRow.createCell(1);
		sevenRowCellTwo.setCellValue(order.getBack());

		HSSFCell sevenRowCellFour = sevenRow.createCell(3);
		sevenRowCellFour.setCellValue(order.getFitting());

		HSSFCell sevenRowCellSix = sevenRow.createCell(5);
		sevenRowCellSix.setCellValue(order.getElectroplate());

		HSSFCell sevenRowCellSeven = sevenRow.createCell(6);
		sevenRowCellSeven.setCellValue("刻模");

		HSSFCell sevenRowCellEight = sevenRow.createCell(7);
		sevenRowCellEight.setCellValue("");

		HSSFCell sevenRowCellNine = sevenRow.createCell(8);
		sevenRowCellNine.setCellValue("");

		// 创建第八行
		HSSFCell eightRowCellSeven = eightRow.createCell(6);
		eightRowCellSeven.setCellValue("压胚");

		HSSFCell eightRowCellEight = eightRow.createCell(7);
		eightRowCellEight.setCellValue("");

		HSSFCell eightRowCellNine = eightRow.createCell(8);
		eightRowCellNine.setCellValue("");

		// 创建第九行
		HSSFCell nineRowCellSeven = nineRow.createCell(6);
		nineRowCellSeven.setCellValue("焊针");

		HSSFCell nineRowCellEight = nineRow.createCell(7);
		nineRowCellEight.setCellValue("");

		HSSFCell nineRowCellNine = nineRow.createCell(8);
		nineRowCellNine.setCellValue("");

		// 创建第十行
		HSSFCell tenRowCellOne = tenRow.createCell(0);
		tenRowCellOne.setCellValue("钻刻");

		HSSFCell tenRowCellTwo = tenRow.createCell(1);
		tenRowCellTwo.setCellValue("树脂");

		HSSFCell tenRowCellFour = tenRow.createCell(3);
		tenRowCellFour.setCellValue("抛光");

		HSSFCell tenRowCellFive = tenRow.createCell(4);
		tenRowCellFive.setCellValue("扫古");

		HSSFCell tenRowCellSix = tenRow.createCell(5);
		tenRowCellSix.setCellValue("包装");

		HSSFCell tenRowCellSeven = tenRow.createCell(6);
		tenRowCellSeven.setCellValue("抛光");

		HSSFCell tenRowCellEight = tenRow.createCell(7);
		tenRowCellEight.setCellValue("");

		HSSFCell tenRowCellNine = tenRow.createCell(8);
		tenRowCellNine.setCellValue("");

		// 创建第十一行
		HSSFCell elevenRowCellOne = elevenRow.createCell(0);
		elevenRowCellOne.setCellValue("");

		HSSFCell elevenRowCellTwo = elevenRow.createCell(1);
		elevenRowCellTwo.setCellValue("");

		HSSFCell elevenRowCellFour = elevenRow.createCell(3);
		elevenRowCellFour.setCellValue(order.getPolishing());

		HSSFCell elevenRowCellFive = elevenRow.createCell(4);
		elevenRowCellFive.setCellValue("");

		HSSFCell elevenRowCellSix = elevenRow.createCell(5);
		elevenRowCellSix.setCellValue(order.getPackages());

		HSSFCell elevenRowCellSeven = elevenRow.createCell(6);
		elevenRowCellSeven.setCellValue("电镀");

		HSSFCell elevenRowCellEight = elevenRow.createCell(7);
		elevenRowCellEight.setCellValue("");

		HSSFCell elevenRowCellNine = elevenRow.createCell(8);
		elevenRowCellNine.setCellValue("");

		// 创建第十二行
		HSSFCell twelveRowCellSeven = twelveRow.createCell(6);
		twelveRowCellSeven.setCellValue("上色");

		HSSFCell twelveRowCellEight = twelveRow.createCell(7);
		twelveRowCellEight.setCellValue("");

		HSSFCell twelveRowCellNine = twelveRow.createCell(8);
		twelveRowCellNine.setCellValue("");

		// 创建第十三行
		HSSFCell thirteenRowCellSeven = thirteenRow.createCell(6);
		thirteenRowCellSeven.setCellValue("包装");

		HSSFCell thirteenRowCellEight = thirteenRow.createCell(7);
		thirteenRowCellEight.setCellValue("");

		HSSFCell thirteenRowCellNine = thirteenRow.createCell(8);
		thirteenRowCellNine.setCellValue("");

		// 创建第十四行
		HSSFCell fourteenRowCellOne = fourteenRow.createCell(0);
		fourteenRowCellOne.setCellValue("图稿");

		// 创建第三十五行
		HSSFCell sixteenRowCellOne = sixteenRow.createCell(0);
		sixteenRowCellOne.setCellValue("备注:");

		HSSFCell sixteenRowCellTwo = sixteenRow.createCell(1);
		sixteenRowCellTwo.setCellValue(order.getRemarks());

		// 创建三十七行
		HSSFCell seventeenRowCellOne = seventeenRow.createCell(0);
		seventeenRowCellOne.setCellValue("跟单:");

		HSSFCell seventeenRowCellTwo = seventeenRow.createCell(1);
		seventeenRowCellTwo.setCellValue(order.getCreateUser());

		HSSFCell seventeenRowCellEight = seventeenRow.createCell(6);
		seventeenRowCellEight.setCellValue("业务员:");

		HSSFCell seventeenRowCellNine = seventeenRow.createCell(7);
		seventeenRowCellNine.setCellValue(order.getCustomerName());

		oneRowCellOne.setCellStyle(style);
		secondRowCellTwo.setCellStyle(wordStyle02);
		secondRowCellSix.setCellStyle(styleMl);
		secondRowCellSeven.setCellStyle(wordStyle03);
		thirdRowCellOne.setCellStyle(styleMl);
		thirdRowCellTwo.setCellStyle(wordStyle03);
		thirdRowCellFive.setCellStyle(styleMl);
		thirdRowCellSix.setCellStyle(wordStyle03);
		fourRowCellOne.setCellStyle(styleMl);
		fourRowCellTwo.setCellStyle(wordStyle03);
		fourRowCellFive.setCellStyle(styleMl);
		fourRowCellSeven.setCellStyle(wordStyle03);
		fiveRowCellOne.setCellStyle(styleMl);
		fiveRowCellRwo.setCellStyle(wordStyle03);
		fiveRowCellThree.setCellStyle(styleMl);
		fiveRowCellFour.setCellStyle(wordStyle03);
		fiveRowCellFive.setCellStyle(styleMl);
		fiveRowCellSix.setCellStyle(wordStyle03);
		sixRowCellOne.setCellStyle(styleM2);
		sixRowCellTwo.setCellStyle(styleM2);
		sixRowCellFour.setCellStyle(styleM2);
		sixRowCellSix.setCellStyle(styleM2);
		sixRowCellSeven.setCellStyle(styleMl);
		sixRowCellEight.setCellStyle(styleMl);
		sixRowCellNine.setCellStyle(styleMl);
		sevenRowCellOne.setCellStyle(wordStyle02);
		sevenRowCellTwo.setCellStyle(wordStyle02);
		sevenRowCellFour.setCellStyle(wordStyle02);
		sevenRowCellSix.setCellStyle(wordStyle02);
		sevenRowCellSeven.setCellStyle(styleMl);
		sevenRowCellEight.setCellStyle(styleMl);
		sevenRowCellNine.setCellStyle(styleMl);
		eightRowCellSeven.setCellStyle(styleMl);
		eightRowCellEight.setCellStyle(styleMl);
		eightRowCellNine.setCellStyle(styleMl);
		nineRowCellSeven.setCellStyle(styleMl);
		nineRowCellEight.setCellStyle(styleMl);
		nineRowCellNine.setCellStyle(styleMl);
		tenRowCellOne.setCellStyle(styleM2);
		tenRowCellTwo.setCellStyle(styleM2);
		tenRowCellFour.setCellStyle(styleM2);
		tenRowCellFive.setCellStyle(styleM2);
		tenRowCellSix.setCellStyle(styleM2);
		tenRowCellSeven.setCellStyle(styleMl);
		tenRowCellEight.setCellStyle(styleMl);
		tenRowCellNine.setCellStyle(styleMl);
		elevenRowCellOne.setCellStyle(styleM2);
		elevenRowCellTwo.setCellStyle(wordStyle02);
		elevenRowCellFour.setCellStyle(wordStyle02);
		elevenRowCellFive.setCellStyle(wordStyle02);
		elevenRowCellSix.setCellStyle(wordStyle02);
		elevenRowCellSeven.setCellStyle(styleMl);
		elevenRowCellEight.setCellStyle(styleMl);
		elevenRowCellNine.setCellStyle(styleMl);
		twelveRowCellSeven.setCellStyle(styleMl);
		twelveRowCellEight.setCellStyle(styleMl);
		twelveRowCellNine.setCellStyle(styleMl);
		thirteenRowCellSeven.setCellStyle(styleMl);
		thirteenRowCellEight.setCellStyle(styleMl);
		thirteenRowCellNine.setCellStyle(styleMl);
		fourteenRowCellOne.setCellStyle(styleMl);
		sixteenRowCellOne.setCellStyle(styleMl);
		sixteenRowCellTwo.setCellStyle(styleRemark);
		seventeenRowCellOne.setCellStyle(styleMl);
		seventeenRowCellTwo.setCellStyle(styleMl);
		seventeenRowCellEight.setCellStyle(styleMl);
		seventeenRowCellNine.setCellStyle(styleMl);

		oneRow.setHeight((short) (20 * 20));
		secondRow.setHeight((short) (15 * 20));
		thirdRow.setHeight((short) (15 * 20));
		fourRow.setHeight((short) (15 * 20));
		fiveRow.setHeight((short) (15 * 20));
		sixRow.setHeight((short) (15 * 20));
		sevenRow.setHeight((short) (15 * 20));
		eightRow.setHeight((short) (15 * 20));
		nineRow.setHeight((short) (15 * 20));
		tenRow.setHeight((short) (15 * 20));
		elevenRow.setHeight((short) (15 * 20));
		twelveRow.setHeight((short) (15 * 20));
		thirteenRow.setHeight((short) (15 * 20));
		fourteenRow.setHeight((short) (15 * 20));
		fifteenRow.setHeight((short) (5 * 20));
		sixteenRow.setHeight((short) (15 * 20));
		seventeenRow.setHeight((short) (15 * 20));

		CellRangeAddress cellOneRangeOne = new CellRangeAddress(0, 0, 0, 8);
		sheet.addMergedRegion(cellOneRangeOne);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, cellOneRangeOne, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, cellOneRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, cellOneRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, cellOneRangeOne, sheet, wb); // 上边框

		CellRangeAddress cellTwoRangeOne = new CellRangeAddress(1, 1, 0, 5);
		sheet.addMergedRegion(cellTwoRangeOne);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, cellTwoRangeOne, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, cellTwoRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, cellTwoRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, cellTwoRangeOne, sheet, wb); // 上边框

		CellRangeAddress cellSixRangeOne = new CellRangeAddress(1, 1, 6, 6);
		sheet.addMergedRegion(cellSixRangeOne);

		CellRangeAddress cellSevenRangeSeven = new CellRangeAddress(1, 1, 7, 8);
		sheet.addMergedRegion(cellSevenRangeSeven);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, cellSevenRangeSeven, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, cellSevenRangeSeven, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, cellSevenRangeSeven, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, cellSevenRangeSeven, sheet, wb); // 上边框

		CellRangeAddress thirdRowCellOneRangeOne = new CellRangeAddress(2, 2, 0, 0);
		sheet.addMergedRegion(thirdRowCellOneRangeOne);

		CellRangeAddress thirdRowCellTwoRangeTwo = new CellRangeAddress(2, 2, 1, 5);
		sheet.addMergedRegion(thirdRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, thirdRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, thirdRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, thirdRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, thirdRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress thirdRowCellFiveRangeFive = new CellRangeAddress(2, 2, 6, 6);
		sheet.addMergedRegion(thirdRowCellFiveRangeFive);

		CellRangeAddress thirdRowCellSixRangeSix = new CellRangeAddress(2, 2, 7, 8);
		sheet.addMergedRegion(thirdRowCellSixRangeSix);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, thirdRowCellSixRangeSix, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, thirdRowCellSixRangeSix, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, thirdRowCellSixRangeSix, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, thirdRowCellSixRangeSix, sheet, wb); // 上边框

		CellRangeAddress fourRowCellOneRangeOne = new CellRangeAddress(3, 3, 0, 0);
		sheet.addMergedRegion(fourRowCellOneRangeOne);

		CellRangeAddress fourRowCellTwoRangeTwo = new CellRangeAddress(3, 3, 1, 5);
		sheet.addMergedRegion(fourRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, fourRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, fourRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, fourRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, fourRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress fourRowCellFiveRangeSix = new CellRangeAddress(3, 3, 6, 6);
		sheet.addMergedRegion(fourRowCellFiveRangeSix);

		CellRangeAddress fourRowCellSevenRangeSeven = new CellRangeAddress(3, 3, 7, 8);
		sheet.addMergedRegion(fourRowCellSevenRangeSeven);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, fourRowCellSevenRangeSeven, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, fourRowCellSevenRangeSeven, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, fourRowCellSevenRangeSeven, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, fourRowCellSevenRangeSeven, sheet, wb); // 上边框

		CellRangeAddress fiveRowCellOneRangeOne = new CellRangeAddress(4, 4, 0, 0);
		sheet.addMergedRegion(fiveRowCellOneRangeOne);

		CellRangeAddress fiveRowCellRwoRangeTwo = new CellRangeAddress(4, 4, 1, 3);
		sheet.addMergedRegion(fiveRowCellRwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, fiveRowCellRwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, fiveRowCellRwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, fiveRowCellRwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, fiveRowCellRwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress fiveRowCellThreeRangeThree = new CellRangeAddress(4, 4, 4, 4);
		sheet.addMergedRegion(fiveRowCellThreeRangeThree);

		CellRangeAddress fiveRowCellFourRangeFour = new CellRangeAddress(4, 4, 5, 5);
		sheet.addMergedRegion(fiveRowCellFourRangeFour);

		CellRangeAddress fiveRowCellFiveRangeFive = new CellRangeAddress(4, 4, 6, 6);
		sheet.addMergedRegion(fiveRowCellFiveRangeFive);

		if (order.getShipDate() != null && !order.getShipDate().equals("")) {
			CellRangeAddress fiveRowCellSixRangeSix = new CellRangeAddress(4, 4, 7, 8);
			sheet.addMergedRegion(fiveRowCellSixRangeSix);
			// 使用RegionUtil类为合并后的单元格添加边框
			RegionUtil.setBorderBottom(1, fiveRowCellSixRangeSix, sheet, wb); // 下边框
			RegionUtil.setBorderLeft(1, fiveRowCellSixRangeSix, sheet, wb); // 左边框
			RegionUtil.setBorderRight(1, fiveRowCellSixRangeSix, sheet, wb); // 有边框
			RegionUtil.setBorderTop(1, fiveRowCellSixRangeSix, sheet, wb); // 上边框
		}

		CellRangeAddress sixRowCellOneRangeOne = new CellRangeAddress(5, 5, 0, 0);
		sheet.addMergedRegion(sixRowCellOneRangeOne);

		CellRangeAddress sixRowCellTwoRangeTwo = new CellRangeAddress(5, 5, 1, 2);
		sheet.addMergedRegion(sixRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sixRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sixRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sixRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sixRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress sixRowCellFourRangeFour = new CellRangeAddress(5, 5, 3, 4);
		sheet.addMergedRegion(sixRowCellFourRangeFour);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sixRowCellFourRangeFour, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sixRowCellFourRangeFour, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sixRowCellFourRangeFour, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sixRowCellFourRangeFour, sheet, wb); // 上边框

		CellRangeAddress sixRowCellSixRangeSix = new CellRangeAddress(5, 5, 5, 5);
		sheet.addMergedRegion(sixRowCellSixRangeSix);

		CellRangeAddress sixRowCellSevenRangeSeven = new CellRangeAddress(5, 5, 6, 6);
		sheet.addMergedRegion(sixRowCellSevenRangeSeven);

		CellRangeAddress sixRowCellEightRangeEight = new CellRangeAddress(5, 5, 7, 7);
		sheet.addMergedRegion(sixRowCellEightRangeEight);

		CellRangeAddress sixRowCellNineRangeNine = new CellRangeAddress(5, 5, 8, 8);
		sheet.addMergedRegion(sixRowCellNineRangeNine);

		CellRangeAddress sevenRowCellOneRangeOne = new CellRangeAddress(6, 8, 0, 0);
		sheet.addMergedRegion(sevenRowCellOneRangeOne);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sevenRowCellOneRangeOne, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sevenRowCellOneRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sevenRowCellOneRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sevenRowCellOneRangeOne, sheet, wb); // 上边框

		CellRangeAddress sevenRowCellTwoRangeTwo = new CellRangeAddress(6, 8, 1, 2);
		sheet.addMergedRegion(sevenRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sevenRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sevenRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sevenRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sevenRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress sevenRowCellFourRangeFour = new CellRangeAddress(6, 8, 3, 4);
		sheet.addMergedRegion(sevenRowCellFourRangeFour);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sevenRowCellFourRangeFour, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sevenRowCellFourRangeFour, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sevenRowCellFourRangeFour, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sevenRowCellFourRangeFour, sheet, wb); // 上边框

		CellRangeAddress sevenRowCellSixRangeSix = new CellRangeAddress(6, 8, 5, 5);
		sheet.addMergedRegion(sevenRowCellSixRangeSix);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sevenRowCellSixRangeSix, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sevenRowCellSixRangeSix, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sevenRowCellSixRangeSix, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sevenRowCellSixRangeSix, sheet, wb); // 上边框

		CellRangeAddress sevenRowCellSevenRangeSeven = new CellRangeAddress(6, 6, 6, 6);
		sheet.addMergedRegion(sevenRowCellSevenRangeSeven);

		CellRangeAddress sevenRowCellEightRangeEight = new CellRangeAddress(6, 6, 7, 7);
		sheet.addMergedRegion(sevenRowCellEightRangeEight);

		CellRangeAddress sevenRowCellNineRangeNine = new CellRangeAddress(6, 6, 8, 8);
		sheet.addMergedRegion(sevenRowCellNineRangeNine);

		CellRangeAddress eightRowCellSevenRangeSeven = new CellRangeAddress(7, 7, 6, 6);
		sheet.addMergedRegion(eightRowCellSevenRangeSeven);

		CellRangeAddress eightRowCellEightRangeEight = new CellRangeAddress(7, 7, 7, 7);
		sheet.addMergedRegion(eightRowCellEightRangeEight);

		CellRangeAddress eightRowCellNineRangeNine = new CellRangeAddress(7, 7, 8, 8);
		sheet.addMergedRegion(eightRowCellNineRangeNine);

		CellRangeAddress nineRowCellSevenRangeSeven = new CellRangeAddress(8, 8, 6, 6);
		sheet.addMergedRegion(nineRowCellSevenRangeSeven);

		CellRangeAddress nineRowCellEightRangeEight = new CellRangeAddress(8, 8, 7, 7);
		sheet.addMergedRegion(nineRowCellEightRangeEight);

		CellRangeAddress nineRowCellNineRangeNine = new CellRangeAddress(8, 8, 8, 8);
		sheet.addMergedRegion(nineRowCellNineRangeNine);

		CellRangeAddress tenRowCellOneRangeOne = new CellRangeAddress(9, 9, 0, 0);
		sheet.addMergedRegion(tenRowCellOneRangeOne);

		CellRangeAddress tenRowCellTwoRangeTwo = new CellRangeAddress(9, 9, 1, 2);
		sheet.addMergedRegion(tenRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, tenRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, tenRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, tenRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, tenRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress tenRowCellFourRangeFour = new CellRangeAddress(9, 9, 3, 3);
		sheet.addMergedRegion(tenRowCellFourRangeFour);

		CellRangeAddress tenRowCellFiveRangeFive = new CellRangeAddress(9, 9, 4, 4);
		sheet.addMergedRegion(tenRowCellFiveRangeFive);

		CellRangeAddress tenRowCellSixRangeSix = new CellRangeAddress(9, 9, 5, 5);
		sheet.addMergedRegion(tenRowCellSixRangeSix);

		CellRangeAddress tenRowCellSevenRangeSeven = new CellRangeAddress(9, 9, 6, 6);
		sheet.addMergedRegion(tenRowCellSevenRangeSeven);

		CellRangeAddress tenRowCellEightRangeEight = new CellRangeAddress(9, 9, 7, 7);
		sheet.addMergedRegion(tenRowCellEightRangeEight);

		CellRangeAddress tenRowCellNineRangeNine = new CellRangeAddress(9, 9, 8, 8);
		sheet.addMergedRegion(tenRowCellNineRangeNine);

		CellRangeAddress elevenRowCellOneRangeOne = new CellRangeAddress(10, 12, 0, 0);
		sheet.addMergedRegion(elevenRowCellOneRangeOne);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, elevenRowCellOneRangeOne, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, elevenRowCellOneRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, elevenRowCellOneRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, elevenRowCellOneRangeOne, sheet, wb); // 上边框

		CellRangeAddress elevenRowCellTwoRangeTwo = new CellRangeAddress(10, 12, 1, 2);
		sheet.addMergedRegion(elevenRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, elevenRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, elevenRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, elevenRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, elevenRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress elevenRowCellFourRangeFour = new CellRangeAddress(10, 12, 3, 3);
		sheet.addMergedRegion(elevenRowCellFourRangeFour);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, elevenRowCellFourRangeFour, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, elevenRowCellFourRangeFour, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, elevenRowCellFourRangeFour, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, elevenRowCellFourRangeFour, sheet, wb); // 上边框

		CellRangeAddress elevenRowCellFiveRangeFive = new CellRangeAddress(10, 12, 4, 4);
		sheet.addMergedRegion(elevenRowCellFiveRangeFive);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, elevenRowCellFiveRangeFive, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, elevenRowCellFiveRangeFive, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, elevenRowCellFiveRangeFive, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, elevenRowCellFiveRangeFive, sheet, wb); // 上边框

		CellRangeAddress elevenRowCellSixRangeSix = new CellRangeAddress(10, 12, 5, 5);
		sheet.addMergedRegion(elevenRowCellSixRangeSix);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, elevenRowCellSixRangeSix, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, elevenRowCellSixRangeSix, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, elevenRowCellSixRangeSix, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, elevenRowCellSixRangeSix, sheet, wb); // 上边框

		CellRangeAddress elevenRowCellSevenRangeSeven = new CellRangeAddress(10, 10, 6, 6);
		sheet.addMergedRegion(elevenRowCellSevenRangeSeven);

		CellRangeAddress elevenRowCellEightRangeEight = new CellRangeAddress(10, 10, 7, 7);
		sheet.addMergedRegion(elevenRowCellEightRangeEight);

		CellRangeAddress elevenRowCellNineRangeNine = new CellRangeAddress(10, 10, 8, 8);
		sheet.addMergedRegion(elevenRowCellNineRangeNine);

		CellRangeAddress twelveRowCellSevenRangeSeven = new CellRangeAddress(11, 11, 6, 6);
		sheet.addMergedRegion(twelveRowCellSevenRangeSeven);

		CellRangeAddress twelveRowCellEightRangeEight = new CellRangeAddress(11, 11, 7, 7);
		sheet.addMergedRegion(twelveRowCellEightRangeEight);

		CellRangeAddress twelveRowCellNineRangeNine = new CellRangeAddress(11, 11, 8, 8);
		sheet.addMergedRegion(twelveRowCellNineRangeNine);

		CellRangeAddress thirteenRowCellSevenRangeSeven = new CellRangeAddress(12, 12, 6, 6);
		sheet.addMergedRegion(thirteenRowCellSevenRangeSeven);

		CellRangeAddress thirteenRowCellEightRangeEight = new CellRangeAddress(12, 12, 7, 7);
		sheet.addMergedRegion(thirteenRowCellEightRangeEight);

		CellRangeAddress thirteenRowCellNineRangeNine = new CellRangeAddress(12, 12, 8, 8);
		sheet.addMergedRegion(thirteenRowCellNineRangeNine);

		CellRangeAddress fourteenRowCellOneRangeOne = new CellRangeAddress(13, 13, 0, 8);
		sheet.addMergedRegion(fourteenRowCellOneRangeOne);
		RegionUtil.setBorderBottom(1, fourteenRowCellOneRangeOne, sheet, wb);
		RegionUtil.setBorderLeft(1, fourteenRowCellOneRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, fourteenRowCellOneRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, fourteenRowCellOneRangeOne, sheet, wb); // 上边框

		// 创建第十五行
		CellRangeAddress fifteenRowRangeOne = new CellRangeAddress(14, 35, 0, 8);
		sheet.addMergedRegion(fifteenRowRangeOne);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, fifteenRowRangeOne, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, fifteenRowRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, fifteenRowRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, fifteenRowRangeOne, sheet, wb); // 上边框

		System.out.println("picUrl--------->>>>>>>>>" + order.getPicUrl());
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		// 将图片写入流中
		BufferedImage bufferImg = ImageIO.read(getInputStream(httpPriffix + order.getPicUrl()));

		ImageIO.write(bufferImg, "PNG", outStream);
		// 利用HSSFPatriarch将图片写入EXCEL
		HSSFPatriarch patri = sheet.createDrawingPatriarch();
		HSSFClientAnchor anchor = new HSSFClientAnchor(50, 50, 50, 50, (short) 0, 14, (short) 8, 35);
		patri.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

		CellRangeAddress sixteenRowCellOneRangeOne = new CellRangeAddress(36, 37, 0, 0);
		sheet.addMergedRegion(sixteenRowCellOneRangeOne);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sixteenRowCellOneRangeOne, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sixteenRowCellOneRangeOne, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sixteenRowCellOneRangeOne, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sixteenRowCellOneRangeOne, sheet, wb); // 上边框

		CellRangeAddress sixteenRowCellTwoRangeTwo = new CellRangeAddress(36, 37, 1, 8);
		sheet.addMergedRegion(sixteenRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, sixteenRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, sixteenRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, sixteenRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, sixteenRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress seventeenRowCellOneRangeOne = new CellRangeAddress(38, 38, 0, 0);
		sheet.addMergedRegion(seventeenRowCellOneRangeOne);

		CellRangeAddress seventeenRowCellTwoRangeTwo = new CellRangeAddress(38, 38, 1, 5);
		sheet.addMergedRegion(seventeenRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, seventeenRowCellTwoRangeTwo, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, seventeenRowCellTwoRangeTwo, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, seventeenRowCellTwoRangeTwo, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, seventeenRowCellTwoRangeTwo, sheet, wb); // 上边框

		CellRangeAddress seventeenRowCellEightRangeEight = new CellRangeAddress(38, 38, 6, 6);
		sheet.addMergedRegion(seventeenRowCellEightRangeEight);

		CellRangeAddress seventeenRowCellNineRangeNine = new CellRangeAddress(38, 38, 7, 8);
		sheet.addMergedRegion(seventeenRowCellNineRangeNine);
		sheet.addMergedRegion(seventeenRowCellTwoRangeTwo);
		// 使用RegionUtil类为合并后的单元格添加边框
		RegionUtil.setBorderBottom(1, seventeenRowCellNineRangeNine, sheet, wb); // 下边框
		RegionUtil.setBorderLeft(1, seventeenRowCellNineRangeNine, sheet, wb); // 左边框
		RegionUtil.setBorderRight(1, seventeenRowCellNineRangeNine, sheet, wb); // 有边框
		RegionUtil.setBorderTop(1, seventeenRowCellNineRangeNine, sheet, wb); // 上边框

		/**
		 * 1、得到所有的合并单元格 sourceSheet.getNumMergedRegions(); 2、得到某一个合并单元格
		 * CellRangeAddress oldRange=sourceSheet.getMergedRegion(i); 3、起始行
		 * oldRange.getFirstRow(); 4、结束行oldRange.getLastRow();
		 * 5、oldRange.getFirstColumn(); 6、oldRange.getLastColumn();
		 */
		int mergeGegions = sheet.getNumMergedRegions();
		System.out.println("mergeGegions------>>>>" + mergeGegions);
		for (int i = 0; i < mergeGegions; i++) {
			org.apache.poi.ss.util.CellRangeAddress oldRange = sheet.getMergedRegion(i);
			int firstRow = oldRange.getFirstRow();
			int lastRow = oldRange.getLastRow();
			int firstColumn = oldRange.getFirstColumn();
			int lastColumn = oldRange.getLastColumn();
			System.out.println("firstRow------>>>>" + firstRow);
			System.out.println("lastRow------>>>>" + lastRow);
			System.out.println("firstColumn------>>>>" + firstColumn);
			System.out.println("lastColumn------>>>>" + lastColumn);
		}

		try {
			String fileName = order.getProductNo() + ".xls";
			FileOutputStream fileOut = new FileOutputStream(fileName);
			wb.write(fileOut);
			fileOut.close();
			System.out.println("输出完成");
			// 页面弹出下载或保存
			response.setContentType("application/x-download");
			response.setHeader("Content-disposition",
					"attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso-8859-1"));
			response.setCharacterEncoding("utf-8");
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.close();
		} catch (Exception e) {
			System.out.println("文件输出失败!");
			e.printStackTrace();
		}

	}

	/**
	 * @param border
	 *            边框宽度
	 * @param region
	 *            合并单元格区域范围
	 * @param sheet
	 * @param wb
	 */
	public static void setRegionBorder(int border, CellRangeAddress region, Sheet sheet, Workbook wb) {
		RegionUtil.setBorderBottom(border, region, sheet, wb);
		RegionUtil.setBorderLeft(border, region, sheet, wb);
		RegionUtil.setBorderRight(border, region, sheet, wb);
		RegionUtil.setBorderTop(border, region, sheet, wb);
	}

	public static boolean isLinux() {
		return System.getProperty("os.name").toLowerCase().indexOf("linux") >= 0;
	}

	public static InputStream getInputStream(String path) {
		InputStream inputStream = null;
		HttpURLConnection httpURLConnection = null;
		try {
			URL url = new URL(path);
			if (url != null) {
				httpURLConnection = (HttpURLConnection) url.openConnection();
				httpURLConnection.setConnectTimeout(3000);
				httpURLConnection.setRequestMethod("GET");
				int responseCode = httpURLConnection.getResponseCode();
				if (responseCode == 200) {
					inputStream = httpURLConnection.getInputStream();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return inputStream;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 *            行下标
	 * @param column
	 *            列下标
	 * @return
	 */
	public static boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			org.apache.poi.ss.util.CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

}
